package homework.mapper;

import homework.entity.Module;
import homework.entity.Role;
import homework.entity.User;
import homework.util.JDBC;
import jdk.nashorn.internal.scripts.JD;

import java.lang.reflect.Parameter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class URMMapper implements Mapper{

    public int insert(User user) {
        int num=0;
        try (Connection conn = JDBC.getConnection()) {
            String sql="insert into user values (?,?)";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,user.getId());
            ps.setString(2,user.getUsername());
            num=ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return num;
    }

        public int delete(int id){
        int num=0;
        try (Connection conn=JDBC.getConnection()
        ){
            String sql="delete from user where id=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            num=ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
            return num;
        }

        public int update (User user){
        int num=0;
        try(Connection conn=JDBC.getConnection()) {
            String sql="update  user set username=? where id=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,user.getUsername());
            ps.setInt(2,user.getId());
            num=ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
            return num;
        }

        public List<User> select(String name) {
        List<User> list=new ArrayList<>();
        try (Connection conn= JDBC.getConnection()){
            String sql="select * from user where username=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,name);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                User user=new User(rs.getInt("id"),rs.getString("username"));
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
            return list;
        }

    @Override
    public List<Role> select1(String name) {
        List<Role> list=new ArrayList<>();
        try(Connection conn =JDBC.getConnection()){
            String sql="select roleid,rolename from user u\n" +
                    "join r_u\n" +
                    "on u.id=r_u.u_id\n" +
                    "join role r\n" +
                    "on r.roleid=r_u.r_id\n" +
                    "where u.username=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,name);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                list.add(new Role(rs.getInt("roleid"),rs.getString("rolename")));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public User select2(String s) {
        User user=null;
        try (Connection conn =JDBC.getConnection()){
            String sql="select u.id,username from user u\n" +
                    "join r_u\n" +
                    "on u.id=r_u.u_id\n" +
                    "join role r\n" +
                    "on r.roleid=r_u.r_id\n" +
                    "where r.rolename=?;";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,s);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                user=new User(rs.getInt("u.id"),rs.getString("username"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return user;
    }

    @Override
    public List<Module> select3(String s) {
        List<Module> list=new ArrayList<>();
        try(Connection conn =JDBC.getConnection()){
            String sql="select m.m_id,m.m_name from r_m\n" +
                    "join module m on r_m.m_id = m.m_id\n" +
                    "join role on role.roleid=r_m.r_id\n" +
                    "where role.rolename=?;";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,s);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                list.add(new Module(rs.getInt("m.m_id"),rs.getString("m.m_name")));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Module> select4(String name) {
        List<Module> list=new ArrayList<>();
        try(Connection conn =JDBC.getConnection()){
            String sql="select m.m_id,m.m_name from user u\n" +
                    "join r_u ru on u.id = ru.u_id\n" +
                    "join role on roleid=ru.r_id\n" +
                    "join r_m rm on roleid = rm.r_id\n" +
                    "join module m on rm.m_id = m.m_id\n" +
                    "where username=?;";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,name);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                list.add(new Module(rs.getInt("m.m_id"),rs.getString("m.m_name")));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public List<User> select5(String s) {
        List<User> list=new ArrayList<>();
        try (Connection conn= JDBC.getConnection()){
            String sql="select u.id,username from user u\n" +
                    "                         join r_u ru on u.id = ru.u_id\n" +
                    "                         join role on roleid=ru.r_id\n" +
                    "                         join r_m rm on roleid = rm.r_id\n" +
                    "                         join module m on rm.m_id = m.m_id\n" +
                    "where m.m_name=?;";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,s);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                User user=new User(rs.getInt("u.id"),rs.getString("username"));
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }


}
